# -*- coding:utf-8 -*-
#  <pre>
# 	@Description 该类文件为操作SQLITE数据库的公共类文件
#  </pre>
#  @copyright www.timotai.net
#  @author 这家伙真懒
#  @date 2016-1-10

import sqlite3
import os
import config
import time

SHOW_SQL = ''
TABLE_NAME = ''
DB_FILE_PATH = ''


def get_conn(path):
    '''获取到数据库的连接对象，参数为数据库文件的绝对路径
    如果传递的参数是存在，并且是文件，那么就返回硬盘上面改
    路径下的数据库文件的连接对象；否则，返回内存中的数据接
    连接对象'''
    conn = sqlite3.connect(path)
    if os.path.exists(path) and os.path.isfile(path):
        #print '硬盘上面:[{}]'.format(path)
        print '硬盘上面:[{}]',path
        return conn
    else:
        conn = None
        print('内存上面:[:memory:]')
        return sqlite3.connect(':memory:')


def get_cursor(conn):
    '''该方法是获取数据库的游标对象，参数为数据库的连接对象
    如果数据库的连接对象不为None，则返回数据库连接对象所创
    建的游标对象；否则返回一个游标对象，该对象是内存中数据
    库连接对象所创建的游标对象'''
    if conn is not None:
        return conn.cursor()
    else:
        return get_conn('').cursor()


###############################################################
####            创建|删除表操作     START
###############################################################
def drop_table(conn, table):
    '''如果表存在,则删除表，如果表中存在数据的时候，使用该
    方法的时候要慎用！'''
    if table is not None and table != '':
        sql = 'DROP TABLE IF EXISTS ' + table
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu = get_cursor(conn)
        cu.execute(sql)
        conn.commit()
        print('删除数据库表[{}]成功!'.format(table))
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(''))


def create_table(conn, sql):
    '''创建数据库表：student'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print '执行sql:[{}]',sql
        cu.execute(sql)
        conn.commit()
        print('创建数据库表成功!')
        close_all(conn, cu)
    else:
        print 'the [{}] is empty or equal None!',sql

def is_table_exist(conn,sql):
    '''查询所有数据'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            #print('执行sql:[{}]'.format(sql))
            print '执行sql:[{}]',sql
        cu.execute(sql)
        r = cu.fetchall()
        if len(r) > 0:
            return False
        else:
            return True
    else:
        print 'the [{}] is table exist!',sql
        return True


def close_all(conn, cu):
    '''关闭数据库游标对象和数据库连接对象'''
    try:
        if cu is not None:
            cu.close()
    finally:
        if cu is not None:
            cu.close()


def save(conn, sql):
    '''插入数据'''
    if sql is not None and sql != '':
            cu = get_cursor(conn)
            if SHOW_SQL:
                print '执行sql:[{}]',sql
            cu.execute(sql)
            conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def fetchall(conn, sql):
    '''查询所有数据'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print '执行sql:[{}]',sql
        cu.execute(sql)
        r = cu.fetchall()
        if len(r) > 0:
            for e in range(len(r)):
                print(r[e])
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def fetchone(conn, sql, data):
    '''查询一条数据'''
    if sql is not None and sql != '':
        if data is not None:
            # Do this instead
            d = (data,)
            cu = get_cursor(conn)
            if SHOW_SQL:
                print '执行sql:[{}],参数:[{}]',sql, data
            cu.execute(sql, d)
            r = cu.fetchall()
            if len(r) > 0:
                for e in range(len(r)):
                    print(r[e])
        else:
            print('the [{}] equal None!'.format(data))
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def update(conn, sql, data):
    '''更新数据'''
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def delete(conn, sql, data):
    '''删除数据'''
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


########################################################################
#           测试操作                                                    #
########################################################################
def drop_table_test():
    '''删除数据库表测试'''
    print('删除数据库表测试...')
    conn = get_conn(DB_FILE_PATH)
    drop_table(conn, TABLE_NAME)


def create_table_test():
    '''创建数据库表测试'''
    print('创建数据库表测试...')
    create_table_sql = '''CREATE TABLE `student` (
                           `id` int(11) NOT NULL,
                           `name` varchar(20) NOT NULL,
                           `gender` varchar(4) DEFAULT NULL,
                           `age` int(11) DEFAULT NULL,
                           `address` varchar(200) DEFAULT NULL,
                           `phone` varchar(20) DEFAULT NULL,
                            PRIMARY KEY (`id`)
                         )'''
    conn = get_conn(DB_FILE_PATH)
    create_table(conn, create_table_sql)


def save_test():
    '''保存数据测试...'''
    print('保存数据测试...')
    save_sql = 'insert into datainfo20151225 (id,HeapSize,FreeMemory,UsedMemory,UpTime,ProcessCpuUsage,CreateCount,DestroyCount,ActiveCount,PoolSize,PercentMaxed,DeclaredThreadHungCount,ClearedThreadHangCount,ConcurrentHungThreadCount,PercentUsed) values (1111,201920,11683,190236,3425,0,0,0,0,0,0,0,0,0,0)'
    conn = get_conn(DB_FILE_PATH)
    save(conn, save_sql)


def fetchall_test():
    '''查询所有数据...'''
    print('查询所有数据...')
    fetchall_sql = '''SELECT * FROM student'''
    conn = get_conn(DB_FILE_PATH)
    fetchall(conn, fetchall_sql)


def fetchone_test():
    '''查询一条数据...'''
    print('查询一条数据...')
    fetchone_sql = 'SELECT * FROM student WHERE ID = ? '
    data = 1
    conn = get_conn(DB_FILE_PATH)
    fetchone(conn, fetchone_sql, data)


def update_test():
    '''更新数据...'''
    print('更新数据...')
    update_sql = 'UPDATE student SET name = ? WHERE ID = ? '
    data = [('HongtenAA', 1),
            ('HongtenBB', 2),
            ('HongtenCC', 3),
            ('HongtenDD', 4)]
    conn = get_conn(DB_FILE_PATH)
    update(conn, update_sql, data)


def delete_test():
    '''删除数据...'''
    print('删除数据...')
    delete_sql = 'DELETE FROM student WHERE NAME = ? AND ID = ? '
    data = [('HongtenAA', 1),
            ('HongtenCC', 3)]
    conn = get_conn(DB_FILE_PATH)
    delete(conn, delete_sql, data)


def init():
    '''初始化方法'''
    # 数据库文件绝句路径
    global DB_FILE_PATH
    DB_FILE_PATH = config.DB_FILE_PATH
    # 数据库表名称   表名+当前时间yyyymmdd
    # 是否打印sql
    global SHOW_SQL
    SHOW_SQL = config.SHOW_SQL
    # print('show_sql : {}'.format(SHOW_SQL))
    # # 如果存在数据库表，则删除表
    # drop_table_test()
    # # 创建数据库表student
    # create_table_test()
    # # 向数据库表中插入数据
    # save_test()

